
#coding=utf-8

import datetime

def monthss(str1, str2):

    year1 = datetime.datetime.strptime(str1, "%Y-%m-%d").year

    year2 = datetime.datetime.strptime(str2, "%Y-%m-%d").year

    month1 = datetime.datetime.strptime(str1, "%Y-%m-%d").month

    month2 = datetime.datetime.strptime(str2, "%Y-%m-%d").month

    num = (year1 - year2) * 12 + (month1 - month2)

    return num



from _pydecimal import Decimal, Context, ROUND_HALF_UP

from decimal import *

full=["14047","13001","13033","10006"]#工作满20年的员工

import xlrd

end_date="2020-11-15"

bk = xlrd.open_workbook(r"D:\20201214.xls")

shxrange = range(bk.nsheets)

try:

    sh = bk.sheet_by_name("Sheet")

    # prin  t(sh)

except:

    print("no sheet in %s named Sheet1")

# 获取行数

nrows = sh.nrows

n=2

'''各项位置'''

clear_18=14

clear_17=15

clear_16=16

clear_15=17

clear_14=18

clear_13=19

'''这一段代表计算了19年当前的年假主要是把数据导入进去'''

for i in range(1, nrows):

    storage=[]#存储员工19年获取的年假



    row_data = sh.row_values(i)

    print(row_data)

    if i==1 or i==2 or row_data[4]=="":



        pass

    else:

        '''写入工号姓名 入职日期'''

        from xlutils.copy import copy  # 导入copy模块



        rb = xlrd.open_workbook('D:\zuizhongjia.xls')  # 打开weng.xls文件

        wb = copy(rb)  # 利用xlutils.copy下的copy函数复制

        ws = wb.get_sheet("Sheet1")  # 获取表单0

        ws.write(n,0,row_data[3])

        ws.write(n, 1, row_data[1])

        ws.write(n, 2, row_data[2])

        ws.write(n, 3, row_data[2])

        ws.write(n, 4, row_data[4])

        ws.write(n, 5, row_data[6])

        '''获取他们19年到限定日期的年假'''



        get_mounth = int(end_date.split("-")[1])#取月份



        get_mounth = get_mounth + 1  # 要循环出最后一个月份



        for i in range(get_mounth):

            if i == 0:

                pass

            else:

                if i < 10:

                    start_date = "2020-0" + str(i) + "-15"

                else:



                    start_date = "2020-" + str(i) + "-15"

                print(start_date,"lalalal")

                #########开始计算年假##########

                months = monthss(start_date, row_data[4])  # 新版本计算月份之前bug

                print(months,start_date,row_data[4],"日期啦啦啦啦")

                b = row_data[4].split("-")#分行处理



                # 保留2位小时需要重新计算



                if row_data[4]>start_date:



                    Year_day=10



                else:





                    if int(b[2]) > 15:

                        months = months - 1



                    mouth_day = months / 12

                    mouth_day = str(mouth_day)#算出他到现在这个日期满多少年

                    print(mouth_day)

                    if row_data[3] in full:

                        '''20年'''

                        storage.append(10)

                        Year_day = 15

                    else:

                        if mouth_day.split(".")[0] == "0":



                            '''转正没有满1年按照10天加'''

                            storage.append(0.83 * 8)

                            Year_day=10

                        else:

                            '''转正满一年'''

                            data_if = row_data[4] < "2016-06-01"

                            '''代表工作年限满1年的'''

                            if data_if:

                                '''16年之前转正的另算'''

                                d1 = "2016-06-01"

                                months = monthss(d1, row_data[4])

                                mouth_day = months / 12  # 算出多少年

                                mouth_day = str(mouth_day)

                                uday = mouth_day.split(".")[0]

                                uday = int(uday)

                                new_2016_data = {"12029": 15, "12040": 15, "13079": 15, "14043": 15, "14094": 15,

                                                 "14178": 12,

                                                 "14204": 12, "15255": 12, "15283": 12, "15376": 12, "15422": 10,

                                                 "15468": 10,

                                                 "15474": 10, }

                                print(row_data,"lalalalwwww")

                                if row_data[3] in new_2016_data.keys():

                                    six_number = new_2016_data[row_data[3]]

                                else:

                                    six_number = 5 + uday

                                if six_number < 10:

                                    six_number = 10

                                else:

                                    pass

                                months = monthss(start_date, d1)

                                mouth_day = months / 12  # 算出多少年

                                mouth_day = str(mouth_day)

                                uday = mouth_day.split(".")[0]

                                Year_day = six_number + int(uday)



                                if Year_day > 15:

                                    Year_day = 15

                                omouth = Year_day / 12

                                leave_days = float(Decimal(str(Year_day / 12)).quantize(Decimal('0.00')))



                                B = leave_days * 8

                                B = float(Decimal(B).quantize(Decimal('0.00')))

                                storage.append(B)

                            else:



                                uday = mouth_day.split(".")[0]

                                uday = int(uday)

                                Year_day = 10 + uday

                                # print(type(Year_day))

                                if Year_day > 15:

                                    Year_day = 15

                                leave_days = float(Decimal(str(Year_day / 12)).quantize(Decimal('0.00')))



                                B = leave_days * 8

                                B = float(Decimal(B).quantize(Decimal('0.00')))

                                storage.append(B)



        under_store=sum(storage)

        if under_store > float(row_data[6]):

            '''代表算出的假期大于现在的余额，所以他们19年之前没有需要清除的年假'''

            under_store=float(row_data[6])

            need_day=0#代表员工总体需要清理的年假



        else:

            need_day = float(row_data[6]) - under_store

        print(Year_day)

        ws.write(n, 6, under_store)

        ws.write(n, 7, need_day)

        ws.write(n, 9, Year_day)

        wb.save('D:\zuizhongjia.xls')

        n+=1





